package com.forum.dao;

import com.forum.bean.Topic;
import com.forum.bean.User;
import com.forum.util.JDBCUtil;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

/**
 * Created by young on 2017/5/28.
 *
 * @version 1.0
 */
public class TopicDao {

    private Connection connection = JDBCUtil.getConnection();


    /**
     * @return list 文章列表
     * @author 杨晓辉
     * 返回主页的文章列表
     */
    public List<Topic> listTopics(int page) {
        List<Topic> list = new ArrayList<>();
        PreparedStatement ps;
        ResultSet set = null;
        try {
            String sql = "SELECT * FROM topic,user WHERE topic.user_id=user.id ORDER BY topic.istop DESC LIMIT ?,?  ";
            ps = connection.prepareStatement(sql);
            ps.setInt(1, (page - 1) * Topic.PAGE_SIZE);
            ps.setInt(2, Topic.PAGE_SIZE);
            set = ps.executeQuery();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return getList(set, list);
    }


    /**
     * @param topic_id 文章id
     * @return 文章列表
     * @author 杨晓辉
     */
    public List<Topic> listTopic(int topic_id) {
        List<Topic> list = new ArrayList<>();
        ResultSet set = null;
        String sql = "SELECT * FROM topic,user WHERE user.id=topic.user_id AND topic.id=? ";
        try {
            PreparedStatement ps = connection.prepareStatement(sql);
            ps.setInt(1, topic_id);
            set = ps.executeQuery();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return getList(set, list);
    }

    /**
     * @param set  Resultset 用于存放数据库集合
     * @param list 接受集合
     * @return 返回list
     * @author 杨晓辉
     */
    private List<Topic> getList(ResultSet set, List<Topic> list) {
        try {
            while (set.next()) {
                Topic topic = new Topic();
                topic.setTopicId(set.getInt("id"));
                topic.setTitle(set.getString("title"));
                topic.setContent(set.getString("content"));
                topic.setDate(set.getString("date"));
                topic.setPoints(set.getString("topic.points"));
                topic.setIsTop(set.getInt("istop"));
                User user = new User();
                user.setId(set.getInt("user.id"));
                user.setUsername(set.getString("username"));
                user.setAge(set.getInt("age"));
                user.setSex(set.getString("sex"));
                user.setPoints(set.getInt("user.points"));
                user.setType(set.getString("type"));
                topic.setUser(user);
                list.add(topic);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return list;
    }

    public int findCount() {
        int count = 0;
        String sql = "SELECT count(*) FROM topic ";
        try {
            PreparedStatement ps = connection.prepareStatement(sql);
            ResultSet set = ps.executeQuery();
            while (set.next()) {
                count = set.getInt(1);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return count;
    }

    /**
     * @param topic  文章
     * @param userId 用户id
     * @return 成功为1 失败为0
     * @author 杨晓辉
     */
    public int insertTpoic(Topic topic, String userId) {
        int row = 0;
        String sql = "INSERT INTO forum.topic (title, content, points, user_id, date) VALUES (?,?,?,?,?);";
        try {
            PreparedStatement ps = connection.prepareStatement(sql);
            ps.setString(1, topic.getTitle());
            ps.setString(2, topic.getContent());
            ps.setString(3, topic.getPoints());
            ps.setString(4, userId);
            ps.setString(5, topic.getDate());
            row = ps.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return row;
    }

    public int deleteTopic(int topic_id) {

        int rs = 0;
        String sqlDeleteComments = "DELETE  FROM comments WHERE  comments.topic_id=? ";
        try {
            PreparedStatement ps = connection.prepareStatement(sqlDeleteComments);
            ps.setInt(1, topic_id);
            ps.executeUpdate();
            String sqlDeleteTopic = "DELETE FROM topic WHERE id=?";
            ps = connection.prepareStatement(sqlDeleteTopic);
            ps.setInt(1, topic_id);
            rs = ps.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return rs;
    }

    public int setTop(int topic_id) {
        int flag = 0;
        String sql = "UPDATE topic SET istop = istop+? WHERE topic.id=?";
        try {
            PreparedStatement ps = connection.prepareStatement(sql);
            ps.setInt(1, 1);
            ps.setInt(2, topic_id);
            flag = ps.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return flag;
    }

    public List<Topic> userTopic(int userId) {
        List<Topic> topicList = new ArrayList<>();
        ResultSet set = null;
        String sql = "SELECT * FROM topic WHERE user_id=?";
        try {
            PreparedStatement ps = connection.prepareStatement(sql);
            ps.setInt(1, userId);
            set = ps.executeQuery();
            while (set.next()) {
                Topic topic = new Topic();
                topic.setTopicId(set.getInt("id"));
                topic.setTitle(set.getString("title"));
                topic.setContent(set.getString("content"));
                topic.setDate(set.getString("date"));
                topic.setPoints(set.getString("points"));
                topicList.add(topic);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }

        return topicList;
    }
}
